import json
from flask import jsonify
from apps import dbUtil
from apps.gpsAndPathUtils import deal_cors


def getMSByIDService(message_ID):
    db = dbUtil.getDbConnection()
    # 使用cursor()方法创建一个游标对象
    cursor = db.cursor()

    # 使用execute()方法执行SQL语句
    cursor.execute('SELECT * FROM message WHERE message_ID LIKE "%' + str(message_ID) + '%";')

    # 使用fetall()获取全部数据
    data = cursor.fetchall()

    # 打印获取到的数据
    # print(len(data))

    # 关闭游标和数据库的连接
    cursor.close()
    db.close()
    # print(data)
    return jsonify({"code": 200, "data": data})


def update_trailer_statusService(data):
    sender = data["sender"]
    sender_type = data["sender_type"]
    receiver = data["receiver"]
    message_content = data["message_content"]

    db = dbUtil.getDbConnection()
    cursor = db.cursor()
    affected_row = cursor.execute(
        'INSERT INTO message(message_ID, sender, sender_type, receiver, message_content) VALUES(null,"' + sender + '",' + str(
            sender_type) + ',"' + receiver + '","' + message_content + '");')

    # print('INSERT INTO message VALUES(message_ID=NULL,sender="'+sender+'",sender_type='+str(sender_type)+',receiver="'+receiver+'",message_content="'+message_content+'");')
    # print('INSERT INTO message VALUERS(sender="'+sender+'",sender_type='+str(sender_type)+',receiver="'+receiver+'",message_content="'+message_content+'");')
    db.commit()
    if affected_row == 1:
        return jsonify({"code": 200, "message": 'success'})
    elif affected_row == 0:
        return jsonify({"code": 200, "message": '信息录入数据库失败', "description": "发送方为user"})


def getTrailerReceiveMSByIDService(trailer_ID):
    trailer_ID = trailer_ID.replace('"', "")
    db = dbUtil.getDbConnection()
    # 使用cursor()方法创建一个游标对象
    cursor = db.cursor()

    # 使用execute()方法执行SQL语句
    cursor.execute('SELECT * FROM message WHERE receiver="' + trailer_ID + '" and sender_type=1;')

    # 使用fetall()获取全部数据
    data = cursor.fetchall()

    # 打印获取到的数据
    # print(len(data))

    # 关闭游标和数据库的连接
    cursor.close()
    db.close()
    # print(data)
    # return jsonify({"code": 200, "data": {"sender":data[:][2]}})
    list = []
    for record in data:
        list.append({"sender": record[1], "content": record[4]})
    print(list)
    return jsonify({"code": 200, "data": list})


def getTrailerSendMSByIDService(trailer_ID):
    trailer_ID = trailer_ID.replace('"', "")
    db = dbUtil.getDbConnection()
    # 使用cursor()方法创建一个游标对象
    cursor = db.cursor()

    # 使用execute()方法执行SQL语句
    cursor.execute('SELECT * FROM message WHERE sender="' + trailer_ID + '" and sender_type=0;')

    # 使用fetall()获取全部数据
    data = cursor.fetchall()

    # 打印获取到的数据
    # print(len(data))

    # 关闭游标和数据库的连接
    cursor.close()
    db.close()
    # print(data)
    # return jsonify({"code": 200, "data": {"sender":data[:][2]}})
    list = []
    for record in data:
        list.append({"receiver": record[3], "content": record[4], "description": "接收方为user"})
    print(list)
    return jsonify({"code": 200, "data": list})


# 获取有关拖车的所有message
def getTrailerAllMessageByIDService(trailer_ID):
    trailer_ID = trailer_ID.replace('"', "")
    db = dbUtil.getDbConnection()
    # 使用cursor()方法创建一个游标对象
    cursor = db.cursor()

    # 使用execute()方法执行SQL语句
    cursor.execute('SELECT * FROM message WHERE receiver="' + trailer_ID + '" OR sender="' + trailer_ID + '";')

    # 使用fetall()获取全部数据
    data = cursor.fetchall()

    # 打印获取到的数据
    # print(len(data))

    # 关闭游标和数据库的连接
    cursor.close()
    db.close()
    # print(data)
    # return jsonify({"code": 200, "data": {"sender":data[:][2]}})
    list = []
    for ms in data:
        record = {}
        record["message_ID"] = ms[0]
        record["sender"] = ms[1]
        record["receiver"] = ms[2]
        record["message_content"] = ms[3]
        list.append(record)
    return deal_cors(jsonify({"code": 200, "data": list}))

# 记录消息信息入数据库
def sendMessage(data):
    sender = data["sender"]
    receiver = data["receiver"]
    message_content = data["message_content"]

    db = dbUtil.getDbConnection()
    cursor = db.cursor()
    statement = 'INSERT INTO message(sender, receiver, message_content) VALUES("{}","{}","{}");'.format(sender, receiver, message_content)
    # print(statement)

    affected_row = cursor.execute(statement)
    db.commit()
    return affected_row


if __name__ == '__main__':
    sendMessage({"sender": "test1", "receiver": "test2", "message_content": "test_message"})
